#!/usr/bin/env python3
#
# Data cobbler for weathercomp.

import collections
import datetime
import sqlite3
import logging

WeatherRecord = collections.namedtuple('WeatherRecord', 
                                       ['location', 
                                        'date',
                                        'source', 
                                        'tempmin', 'tempmax', 
                                        'tempmin1', 'tempmin2', 'tempmin4',
                                        'tempmax1', 'tempmax2', 'tempmax4'])

class Weather:
    """Represents a collection of weather records. 

    All forecasts and the report for a given date, location and data source
    are stored in one row.
    """
    def __init__(self, db='weathercomp.db'):
        """Create a Weather instance.

        Cobbles together data from tables Forecast, Report and Location into
        one big table with rows matching a WeatherRecord.

        """
        self.view = sqlite3.connect(':memory:', detect_types=sqlite3.PARSE_DECLTYPES)
        self.view.row_factory = sqlite3.Row
        source = sqlite3.connect(db, detect_types=sqlite3.PARSE_COLNAMES)
        source.row_factory = sqlite3.Row

        report_q = """
            SELECT Location.LocID as LocID, Location.Name as loc, Date as "d [date]", 
            TempMin, TempMax 
                FROM Report, Location
                WHERE Location.LocID = Report.LocID
                ORDER BY Location.LocID, Date;"""
        forecast_q = """
            SELECT Source, julianday(:date) - julianday(ObservationDate) as daysahead, 
            TempMin, TempMax 
                FROM Forecast
                WHERE ForecastDate = :date AND LocID = :locid;"""
        view_create_q = """
            CREATE TABLE Weather (
                Location TEXT, 
                Date date,
                Source TEXT, 
                TempMin INTEGER, TempMax INTEGER,
                TempMin1 INTEGER, TempMin2 INTEGER, TempMin4 INTEGER,
                TempMax1 INTEGER, TempMax2 INTEGER, TempMax4 INTEGER
            );"""
        view_insert_q = """
            INSERT INTO Weather VALUES (
                :location, :date, :source,
                :tempmin, :tempmax,
                :tempmin1, :tempmin2, :tempmin4,
                :tempmax1, :tempmax2, :tempmax4
            );"""

        self.view.execute(view_create_q)

        records = []
        for report in source.execute(report_q):
            # This could be achieved using a VIEW, but while creating a VIEW
            # is pretty fast, querying it turned out to be very slow.
            forecasts = dict()
            for forecast in source.execute(forecast_q, {
                    'date': report['d'], 
                    'locid': report['LocID']}):
                datasource = forecast['Source']
                if datasource not in forecasts:
                    forecasts[datasource] = {'min': {1: 0, 2: 0, 4: 0}, 'max': {1: 0, 2: 0, 4: 0}}
                forecasts[datasource]['min'][forecast['daysahead']] = forecast['TempMin']
                forecasts[datasource]['max'][forecast['daysahead']] = forecast['TempMax']
            for datasource, minmax in forecasts.items():
                 records.append({
                        'location': report['loc'],
                        'date': report['d'],
                        'source': datasource,
                        'tempmin': report['TempMin'],
                        'tempmax': report['TempMax'],
                        'tempmin1': minmax['min'][1],
                        'tempmin2': minmax['min'][2],
                        'tempmin4': minmax['min'][4],
                        'tempmax1': minmax['max'][1],
                        'tempmax2': minmax['max'][2],
                        'tempmax4': minmax['max'][4]
                        })
        self.view.executemany(view_insert_q, records)
        source.close()

    def locations(self):
        location_q = """SELECT DISTINCT Location FROM Weather"""
        return [row['Location'] for row in self.view.execute(location_q)]

    def sources(self):
        source_q = """SELECT DISTINCT Source FROM WEATHER"""
        return [row['Source'] for row in self.view.execute(source_q)]

    def query_raw(self, where="", params={}):
        """Query the database with the provided raw constraints.  

        `where` can be anything that would go in the place of the WHERE clause
        - valid values include `WHERE 1=1`, `WHERE Location = 'Auckland, New
        Zealand' LIMIT 20` or an empty string.  `params` is directly passed to
        `.execute` so sqlite3 placeholder syntax will work.

        Returns a list of matching WeatherRecords.

        """
        query_base = """
            SELECT Location, Date, Source,
            TempMin, TempMax, 
            TempMin1, TempMin2, TempMin4, 
            TempMax1, TempMax2, TempMax4 
            FROM Weather """
        query = query_base + where
        return [WeatherRecord(*row) for row in self.view.execute(query, params)]

    def query(self, location="%", source="%", 
              mindate=datetime.date(1000, 1, 1), 
              maxdate=datetime.date(datetime.MAXYEAR, 1, 1)):
        """Query the database with the provided high-level constraints.

        `location`: The desired location, full name.
        `source`:   The source of the data - for instance 'weatherbug' or 'wwonline'.
        `mindate`:  Lower inclusive limit for the record date.
        `maxdate`:  Upper inclusive limit for the record date.

        All options default to "everything ever".  Returns a list of matching
        WeatherRecords.

        """
        query = """
            WHERE Location LIKE :location
            AND Source LIKE :source
            AND julianday(Date) >= julianday(:mindate)
            AND julianday(Date) <= julianday(:maxdate);"""

        params = {
            'location': location,
            'source': source,
            'mindate': mindate,
            'maxdate': maxdate
            }

        return self.query_raw(query, params)

if __name__ == '__main__':
    w = Weather()
    print('Sources:')
    print(w.sources())
    print('Locations:')
    print(w.locations())
    
    # Fetch records from Auckland, with forecasts from weatherbug over the past seven days.
    print(w.query(
            location='Auckland, New Zealand', 
            source='weatherbug',
            mindate=datetime.date.today() - datetime.timedelta(days=7)))
    

